// 导入必要的包
const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');
const dotenv = require('dotenv');

// 尝试加载.env.local文件
const envPath = path.resolve(process.cwd(), '.env.local');
dotenv.config({ path: envPath });

// 如果环境变量未加载成功，尝试其他路径
if (!process.env.DB_HOST) {
  const parentEnvPath = path.resolve(process.cwd(), '..', '.env.local');
  if (fs.existsSync(parentEnvPath)) {
    dotenv.config({ path: parentEnvPath });
    console.log(`已从上级目录加载环境变量: ${parentEnvPath}`);
  }
}

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || 'rm-bp14wd51i709c3z79ho.mysql.rds.aliyuncs.com',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'Ircnjfdhnvxwz@123',
  port: process.env.DB_PORT || 3306,
  multipleStatements: true, // 允许执行多条SQL语句
  connectTimeout: 15000, // 15秒连接超时
};

console.log('数据库连接信息:');
console.log(`主机: ${dbConfig.host}`);
console.log(`用户: ${dbConfig.user}`);
console.log(`端口: ${dbConfig.port}`);

async function main() {
  let connection;

  try {
    // 连接到阿里云数据库服务器（不选择具体数据库）
    console.log('正在连接到阿里云数据库...');
    connection = await mysql.createConnection(dbConfig);

    // 创建数据库
    const dbName = process.env.DB_NAME || 'xiaoyoubao';
    console.log(`正在创建数据库 ${dbName}...`);
    await connection.query(`CREATE DATABASE IF NOT EXISTS ${dbName}
                          CHARACTER SET utf8mb4
                          COLLATE utf8mb4_unicode_ci`);

    // 选择数据库
    await connection.query(`USE ${dbName}`);
    console.log(`成功选择数据库: ${dbName}`);

    // 创建基础表结构 - 先尝试创建users表
    console.log('正在创建基础表结构...');

    try {
      await connection.query(`
        CREATE TABLE IF NOT EXISTS users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          username VARCHAR(50) NOT NULL UNIQUE,
          password VARCHAR(255) NOT NULL,
          email VARCHAR(100) NOT NULL UNIQUE,
          avatar VARCHAR(255),
          bio TEXT,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        );

        CREATE INDEX IF NOT EXISTS idx_username ON users(username);
        CREATE INDEX IF NOT EXISTS idx_email ON users(email);
      `);
      console.log('成功创建用户表');
    } catch (tableError) {
      console.error('创建用户表时出错:', tableError.message);
      // 继续执行，不中断程序
    }

    // 创建扩展表结构
    console.log('正在创建扩展表结构...');

    // 用户详细信息表
    try {
      await connection.query(`
        CREATE TABLE IF NOT EXISTS user_profiles (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          real_name VARCHAR(50),
          gender ENUM('male', 'female', 'other') DEFAULT 'other',
          birth_date DATE,
          phone VARCHAR(20),
          address VARCHAR(255),
          city VARCHAR(50),
          province VARCHAR(50),
          country VARCHAR(50) DEFAULT 'China',
          postal_code VARCHAR(20),
          personal_website VARCHAR(255),
          social_links JSON,
          is_verified BOOLEAN DEFAULT FALSE,
          verification_type VARCHAR(50),
          verification_time DATETIME,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_user_id_profiles ON user_profiles(user_id);
      `);
      console.log('成功创建用户详细信息表');
    } catch (profileError) {
      console.error('创建用户详细信息表时出错:', profileError.message);
    }

    // 教育经历表
    try {
      await connection.query(`
        CREATE TABLE IF NOT EXISTS educations (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          school_name VARCHAR(100) NOT NULL,
          degree VARCHAR(50),
          field_of_study VARCHAR(100),
          start_date DATE,
          end_date DATE,
          is_current BOOLEAN DEFAULT FALSE,
          description TEXT,
          activities TEXT,
          achievements TEXT,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_user_id_educations ON educations(user_id);
        CREATE INDEX IF NOT EXISTS idx_school_name ON educations(school_name);
      `);
      console.log('成功创建教育经历表');
    } catch (eduError) {
      console.error('创建教育经历表时出错:', eduError.message);
    }

    // 工作经历表
    try {
      await connection.query(`
        CREATE TABLE IF NOT EXISTS work_experiences (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          company_name VARCHAR(100) NOT NULL,
          position VARCHAR(100) NOT NULL,
          location VARCHAR(100),
          start_date DATE,
          end_date DATE,
          is_current BOOLEAN DEFAULT FALSE,
          description TEXT,
          responsibilities TEXT,
          achievements TEXT,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_user_id_work ON work_experiences(user_id);
        CREATE INDEX IF NOT EXISTS idx_company_name ON work_experiences(company_name);
      `);
      console.log('成功创建工作经历表');
    } catch (workError) {
      console.error('创建工作经历表时出错:', workError.message);
    }

    // 技能表
    try {
      await connection.query(`
        CREATE TABLE IF NOT EXISTS skills (
          id INT AUTO_INCREMENT PRIMARY KEY,
          user_id INT NOT NULL,
          skill_name VARCHAR(100) NOT NULL,
          proficiency ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'intermediate',
          years_of_experience INT,
          is_highlighted BOOLEAN DEFAULT FALSE,
          created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        );

        CREATE INDEX IF NOT EXISTS idx_user_id_skills ON skills(user_id);
        CREATE INDEX IF NOT EXISTS idx_skill_name ON skills(skill_name);
      `);
      console.log('成功创建技能表');
    } catch (skillError) {
      console.error('创建技能表时出错:', skillError.message);
    }

    console.log('数据库初始化完成！');

  } catch (error) {
    console.error('设置数据库时发生错误:', error.message);
    console.log('\n请检查以下可能的问题:');
    console.log('1. 确认数据库服务器地址和端口是否正确');
    console.log('2. 确认用户名和密码是否正确');
    console.log('3. 检查网络连接是否正常');
    console.log('4. 检查防火墙设置是否允许数据库连接');
    console.log('5. 确认已在阿里云控制台设置IP白名单');
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

main().catch(err => {
  console.error('执行过程中发生未捕获的错误:', err);
  process.exit(1);
});